package v1

import (
	"archive/zip"
	"fmt"
	"io"
	"net/http"
	"os"
	"path/filepath"
	"strings"
	"time"

	"github.com/hay-kot/httpkit/errchain"
	"github.com/hay-kot/httpkit/server"
	"github.com/sysadminsmedia/homebox/backend/internal/core/services"
	"github.com/sysadminsmedia/homebox/backend/pkgs/sqldump"
)

// replaceFileWithRetry attempts to replace a file with retry logic for Windows compatibility
func replaceFileWithRetry(targetPath, sourcePath string) error {
	maxRetries := 5
	retryDelay := 200 * time.Millisecond

	for attempt := 0; attempt < maxRetries; attempt++ {
		// Create backup of current file
		backupTempPath := targetPath + ".backup"
		if _, err := os.Stat(targetPath); err == nil {
			// Move current file to backup location
			err = os.Rename(targetPath, backupTempPath)
			if err != nil {
				if attempt < maxRetries-1 {
					time.Sleep(retryDelay)
					continue
				}
				return fmt.Errorf("failed to backup current file after %d attempts: %w", maxRetries, err)
			}
		}

		// Move new file to target location
		err := os.Rename(sourcePath, targetPath)
		if err != nil {
			// Restore backup if rename failed
			if _, backupExists := os.Stat(backupTempPath); backupExists == nil {
				os.Rename(backupTempPath, targetPath)
			}
			
			if attempt < maxRetries-1 {
				time.Sleep(retryDelay)
				continue
			}
			return fmt.Errorf("failed to replace file after %d attempts: %w", maxRetries, err)
		}

		// Success - clean up backup temp file
		os.Remove(backupTempPath)
		return nil
	}

	return fmt.Errorf("failed to replace file after %d attempts", maxRetries)
}

// HandleDataExportDownload godoc
//
//	@Summary	Export Data Directory as ZIP for Download
//	@Tags		Tools
//	@Produce	application/zip
//	@Success	200	{string}	string	"application/zip"
//	@Router		/v1/tools/data/export/download [GET]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDataExportDownload() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		dataDir := "./data"

		// Check if data directory exists
		if _, err := os.Stat(dataDir); os.IsNotExist(err) {
			return fmt.Errorf("data directory does not exist")
		}

		// Set headers for file download
		filename := fmt.Sprintf("homebox-data-%s.zip", time.Now().Format("2006-01-02-15-04-05"))
		w.Header().Set("Content-Type", "application/zip")
		w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", filename))

		// Create zip writer
		zipWriter := zip.NewWriter(w)
		defer zipWriter.Close()

		// Walk through data directory and add files to zip
		err := filepath.Walk(dataDir, func(path string, info os.FileInfo, err error) error {
			if err != nil {
				return err
			}

			if info.IsDir() {
				return nil
			}

			// Create zip file header
			header, err := zip.FileInfoHeader(info)
			if err != nil {
				return err
			}

			// Make the path relative to data directory and ensure forward slashes
			relPath, err := filepath.Rel(dataDir, path)
			if err != nil {
				return err
			}
			header.Name = filepath.ToSlash(relPath)

			// Create writer in zip
			writer, err := zipWriter.CreateHeader(header)
			if err != nil {
				return err
			}

			// Open source file
			srcFile, err := os.Open(path)
			if err != nil {
				return err
			}
			defer srcFile.Close()

			// Copy file content to zip
			_, err = io.Copy(writer, srcFile)
			return err
		})

		if err != nil {
			return fmt.Errorf("failed to create zip: %w", err)
		}

		return nil
	}
}

// HandleDataExportSave godoc
//
//	@Summary	Export Data Directory as ZIP and Save to Backup Directory
//	@Tags		Tools
//	@Produce	json
//	@Success	200	{object}	map[string]string	"{\"message\": \"backup created\", \"filename\": \"homebox-data-2024-01-01-12-00-00.zip\"}"
//	@Router		/v1/tools/data/export/save [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDataExportSave() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		dataDir := "./data"
		backupDir := "./backup"

		// Check if data directory exists
		if _, err := os.Stat(dataDir); os.IsNotExist(err) {
			return fmt.Errorf("data directory does not exist")
		}

		// Create backup directory if it doesn't exist
		if err := os.MkdirAll(backupDir, 0755); err != nil {
			return fmt.Errorf("failed to create backup directory: %w", err)
		}

		// Generate filename with current timestamp
		filename := fmt.Sprintf("homebox-data-%s.zip", time.Now().Format("2006-01-02-15-04-05"))
		backupPath := filepath.Join(backupDir, filename)

		// Create backup file
		backupFile, err := os.Create(backupPath)
		if err != nil {
			return fmt.Errorf("failed to create backup file: %w", err)
		}
		defer backupFile.Close()

		// Create zip writer
		zipWriter := zip.NewWriter(backupFile)
		defer zipWriter.Close()

		// Walk through data directory and add files to zip
		err = filepath.Walk(dataDir, func(path string, info os.FileInfo, err error) error {
			if err != nil {
				return err
			}

			if info.IsDir() {
				return nil
			}

			// 获取相对于data目录的路径
			relPath, err := filepath.Rel(dataDir, path)
			if err != nil {
				return err
			}

			// 跳过根目录下的文件（如homebox.db等），只导出子目录中的文件
			if filepath.Dir(relPath) == "." {
				return nil // 跳过根目录文件
			}

			// Create zip file header
			header, err := zip.FileInfoHeader(info)
			if err != nil {
				return err
			}

			// Make the path relative to data directory and ensure forward slashes
			header.Name = filepath.ToSlash(relPath)

			// Create writer in zip
			writer, err := zipWriter.CreateHeader(header)
			if err != nil {
				return err
			}

			// Open source file
			srcFile, err := os.Open(path)
			if err != nil {
				return err
			}
			defer srcFile.Close()

			// Copy file content to zip
			_, err = io.Copy(writer, srcFile)
			return err
		})

		if err != nil {
			return fmt.Errorf("failed to create backup: %w", err)
		}

		// Return success response
		return server.JSON(w, http.StatusOK, map[string]string{
			"message":  "backup created",
			"filename": filename,
		})
	}
}

// HandleDataImportUpload godoc
//
//	@Summary	Import Data Directory from Uploaded ZIP
//	@Tags		Tools
//	@Accept		multipart/form-data
//	@Produce	json
//	@Param		file	formData	file	true	"ZIP file to import"
//	@Success	200	{object}	map[string]string	"{\"message\": \"import successful\"}"
//	@Router		/v1/tools/data/import/upload [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDataImportUpload() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		dataDir := "./data"

		// Parse multipart form
		err := r.ParseMultipartForm(100 << 20) // 100MB max
		if err != nil {
			return fmt.Errorf("failed to parse form: %w", err)
		}

		// Get file from form data
		file, header, err := r.FormFile("file")
		if err != nil {
			return fmt.Errorf("failed to get file: %w", err)
		}
		defer file.Close()

		// Validate file type
		if filepath.Ext(header.Filename) != ".zip" {
			return fmt.Errorf("only ZIP files are allowed")
		}

		// Create temporary directory for extraction
		tempDir, err := os.MkdirTemp("", "homebox-import-*")
		if err != nil {
			return fmt.Errorf("failed to create temp directory: %w", err)
		}
		defer os.RemoveAll(tempDir)

		// Create backup of current data directory before import
		if _, err := os.Stat(dataDir); err == nil {
			backupTimestamp := time.Now().Format("2006-01-02-15-04-05")
			backupName := fmt.Sprintf("pre-import-backup-%s", backupTimestamp)
			backupPath := filepath.Join("./backup", backupName)

			if err := createBackup(dataDir, backupPath); err != nil {
				return fmt.Errorf("failed to create pre-import backup: %w", err)
			}
		}

		// 使用系统命令进行原子替换
		// 在Windows上，我们需要重启服务才能完全替换数据
		// 因此我们将数据放在临时位置，并返回特殊标记
		
		// 创建标记文件指示需要重启
		restartMarker := filepath.Join("./", "RESTART_REQUIRED")
		if err := os.WriteFile(restartMarker, []byte(tempDir), 0644); err != nil {
			return fmt.Errorf("failed to create restart marker: %w", err)
		}

		return server.JSON(w, http.StatusOK, map[string]string{
			"message": "import prepared - please restart the service to complete",
			"restart_required": "true",
		})
	}
}

// HandleDataImportBackup godoc
//
//	@Summary	Import Data Directory from Backup File
//	@Tags		Tools
//	@Produce	json
//	@Param		filename	query	string	true	"Backup filename to import"
//	@Success	200	{object}	map[string]string	"{\"message\": \"import successful\"}"
//	@Router		/v1/tools/data/import/backup [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDataImportBackup() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		dataDir := "./data"
		backupDir := "./backup"

		// Get filename from query parameter
		filename := r.URL.Query().Get("filename")
		if filename == "" {
			return fmt.Errorf("filename is required")
		}

		// Validate filename to prevent directory traversal
		if strings.Contains(filename, "..") || strings.Contains(filename, "/") || strings.Contains(filename, "\\") {
			return fmt.Errorf("invalid filename")
		}

		backupPath := filepath.Join(backupDir, filename)

		// Check if backup file exists
		if _, err := os.Stat(backupPath); os.IsNotExist(err) {
			return fmt.Errorf("backup file does not exist")
		}

		// Create backup of current data directory before import
		if _, err := os.Stat(dataDir); err == nil {
			backupTimestamp := time.Now().Format("2006-01-02-15-04-05")
			backupName := fmt.Sprintf("pre-restore-backup-%s", backupTimestamp)
			newBackupPath := filepath.Join(backupDir, backupName)

			if err := createBackup(dataDir, newBackupPath); err != nil {
				return fmt.Errorf("failed to create pre-restore backup: %w", err)
			}
		}

		// Open backup file
		backupFile, err := os.Open(backupPath)
		if err != nil {
			return fmt.Errorf("failed to open backup file: %w", err)
		}
		defer backupFile.Close()

		// 创建临时目录进行导入
		tempImportDir, err := os.MkdirTemp("./", "homebox-restore-")
		if err != nil {
			return fmt.Errorf("failed to create temp restore directory: %w", err)
		}
		defer os.RemoveAll(tempImportDir)

		// 提取ZIP到临时目录
		if err := extractZIPWithDB(backupFile, tempImportDir); err != nil {
			return fmt.Errorf("failed to extract backup to temp directory: %w", err)
		}

		// 创建标记文件指示需要重启
		restartMarker := filepath.Join("./", "RESTART_REQUIRED")
		if err := os.WriteFile(restartMarker, []byte(tempImportDir), 0644); err != nil {
			return fmt.Errorf("failed to create restart marker: %w", err)
		}

		return server.JSON(w, http.StatusOK, map[string]string{
			"message": "restore prepared - please restart the service to complete",
			"restart_required": "true",
		})
	}
}

// HandleDatabaseExportDownload godoc
//
//	@Summary	Export SQLite Database for Download
//	@Tags		Tools
//	@Produce	application/octet-stream
//	@Success	200	{string}	string	"application/octet-stream"
//	@Router		/v1/tools/database/export/download [GET]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDatabaseExportDownload() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Get database path from config
		dbPath := ctrl.config.Database.SqlitePath
		if dbPath == "" {
			dbPath = "./data/homebox.db"
		} else {
			// Remove query parameters
			dbPath = strings.Split(dbPath, "?")[0]
		}

		// Check if database file exists
		if _, err := os.Stat(dbPath); os.IsNotExist(err) {
			return fmt.Errorf("database file does not exist: %s", dbPath)
		}

		// Set headers for file download
		filename := fmt.Sprintf("homebox-database-%s.db", time.Now().Format("2006-01-02-15-04-05"))
		w.Header().Set("Content-Type", "application/octet-stream")
		w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", filename))

		// Open and serve the database file
		dbFile, err := os.Open(dbPath)
		if err != nil {
			return fmt.Errorf("failed to open database file: %w", err)
		}
		defer dbFile.Close()

		// Copy file content to response
		_, err = io.Copy(w, dbFile)
		if err != nil {
			return fmt.Errorf("failed to copy database file: %w", err)
		}

		return nil
	}
}

// HandleDatabaseExportSave godoc
//
//	@Summary	Export SQLite Database and Save to Backup Directory
//	@Tags		Tools
//	@Produce	json
//	@Success	200	{object}	map[string]string	"{\"message\": \"database exported\", \"filename\": \"homebox-database-2024-01-01-12-00-00.db\"}"
//	@Router		/v1/tools/database/export/save [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDatabaseExportSave() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Get database path from config
		dbPath := ctrl.config.Database.SqlitePath
		if dbPath == "" {
			dbPath = "./data/homebox.db"
		} else {
			// Remove query parameters
			dbPath = strings.Split(dbPath, "?")[0]
		}

		// Check if database file exists
		if _, err := os.Stat(dbPath); os.IsNotExist(err) {
			return fmt.Errorf("database file does not exist: %s", dbPath)
		}

		// Create backup directory if it doesn't exist
		backupDir := "./backup"
		if err := os.MkdirAll(backupDir, 0755); err != nil {
			return fmt.Errorf("failed to create backup directory: %w", err)
		}

		// Generate filename with current timestamp
		filename := fmt.Sprintf("homebox-database-%s.db", time.Now().Format("2006-01-02-15-04-05"))
		backupPath := filepath.Join(backupDir, filename)

		// Copy database file to backup location
		srcFile, err := os.Open(dbPath)
		if err != nil {
			return fmt.Errorf("failed to open source database: %w", err)
		}
		defer srcFile.Close()

		dstFile, err := os.Create(backupPath)
		if err != nil {
			return fmt.Errorf("failed to create backup file: %w", err)
		}
		defer dstFile.Close()

		_, err = io.Copy(dstFile, srcFile)
		if err != nil {
			return fmt.Errorf("failed to copy database: %w", err)
		}

		// Return success response
		return server.JSON(w, http.StatusOK, map[string]string{
			"message":  "database exported",
			"filename": filename,
		})
	}
}

// HandleDatabaseImportUpload godoc
//
//	@Summary	Import SQLite Database from Uploaded File
//	@Tags		Tools
//	@Accept		multipart/form-data
//	@Param		file	formData	file	true	"Database file (.db)"
//	@Produce	json
//	@Success	200	{object}	map[string]string	"{\"message\": \"database import prepared\"}"
//	@Router		/v1/tools/database/import/upload [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDatabaseImportUpload() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())

		// Parse multipart form
		err := r.ParseMultipartForm(10 << 20) // 10MB max
		if err != nil {
			return fmt.Errorf("failed to parse multipart form: %w", err)
		}

		// Get uploaded file
		file, _, err := r.FormFile("file")
		if err != nil {
			return fmt.Errorf("failed to get uploaded file: %w", err)
		}
		defer file.Close()

		// Get current database path
		dbPath := ctrl.config.Database.SqlitePath
		if dbPath == "" {
			dbPath = "./data/homebox.db"
		} else {
			// Remove query parameters
			dbPath = strings.Split(dbPath, "?")[0]
		}

		// Create backup of current database before import
		if _, err := os.Stat(dbPath); err == nil {
			backupTimestamp := time.Now().Format("2006-01-02-15-04-05")
			backupName := fmt.Sprintf("pre-db-import-backup-%s.db", backupTimestamp)
			backupPath := filepath.Join("./backup", backupName)

			// Create backup directory if needed
			if err := os.MkdirAll("./backup", 0755); err != nil {
				return fmt.Errorf("failed to create backup directory: %w", err)
			}

			// Copy current database to backup
			srcFile, err := os.Open(dbPath)
			if err != nil {
				return fmt.Errorf("failed to open current database: %w", err)
			}
			defer srcFile.Close()

			backupFile, err := os.Create(backupPath)
			if err != nil {
				return fmt.Errorf("failed to create backup file: %w", err)
			}
			defer backupFile.Close()

			_, err = io.Copy(backupFile, srcFile)
			if err != nil {
				return fmt.Errorf("failed to backup current database: %w", err)
			}
		}

		// Create temporary file for the uploaded database
		tempFile, err := os.CreateTemp("./", "homebox-db-import-*.db")
		if err != nil {
			return fmt.Errorf("failed to create temporary file: %w", err)
		}
		tempPath := tempFile.Name()
		defer tempFile.Close()

		// Copy uploaded file to temporary location
		_, err = io.Copy(tempFile, file)
		if err != nil {
			os.Remove(tempPath)
			return fmt.Errorf("failed to save uploaded file: %w", err)
		}

		// Create restart marker with temporary database path
		restartMarker := filepath.Join("./", "RESTART_REQUIRED_DB")
		if err := os.WriteFile(restartMarker, []byte(tempPath), 0644); err != nil {
			os.Remove(tempPath)
			return fmt.Errorf("failed to create restart marker: %w", err)
		}

		return server.JSON(w, http.StatusOK, map[string]string{
			"message": "database import prepared - please restart the service to complete",
			"restart_required": "true",
		})
	}
}

// HandleDatabaseImportBackup godoc
//
//	@Summary	Import SQLite Database from Backup File
//	@Tags		Tools
//	@Produce	json
//	@Param		filename	query	string	true	"Database backup filename to import"
//	@Success	200	{object}	map[string]string	"{\"message\": \"database import prepared\"}"
//	@Router		/v1/tools/database/import/backup [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDatabaseImportBackup() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Get filename from query parameter
		filename := r.URL.Query().Get("filename")
		if filename == "" {
			return fmt.Errorf("filename is required")
		}

		// Validate filename to prevent directory traversal
		if strings.Contains(filename, "..") || strings.Contains(filename, "/") || strings.Contains(filename, "\\") {
			return fmt.Errorf("invalid filename")
		}

		// Check if it's a database file
		if !strings.HasSuffix(filename, ".db") {
			return fmt.Errorf("only .db files are allowed")
		}

		backupDir := "./backup"
		backupPath := filepath.Join(backupDir, filename)

		// Check if backup file exists
		if _, err := os.Stat(backupPath); os.IsNotExist(err) {
			return fmt.Errorf("backup database file does not exist")
		}

		// Get current database path
		dbPath := ctrl.config.Database.SqlitePath
		if dbPath == "" {
			dbPath = "./data/homebox.db"
		} else {
			// Remove query parameters
			dbPath = strings.Split(dbPath, "?")[0]
		}

		// Create backup of current database before restore
		if _, err := os.Stat(dbPath); err == nil {
			backupTimestamp := time.Now().Format("2006-01-02-15-04-05")
			backupName := fmt.Sprintf("pre-db-restore-backup-%s.db", backupTimestamp)
			newBackupPath := filepath.Join(backupDir, backupName)

			// Copy current database to backup
			srcFile, err := os.Open(dbPath)
			if err != nil {
				return fmt.Errorf("failed to open current database: %w", err)
			}
			defer srcFile.Close()

			backupFile, err := os.Create(newBackupPath)
			if err != nil {
				return fmt.Errorf("failed to create backup file: %w", err)
			}
			defer backupFile.Close()

			_, err = io.Copy(backupFile, srcFile)
			if err != nil {
				return fmt.Errorf("failed to backup current database: %w", err)
			}
		}

		// Create temporary copy of backup database
		tempFile, err := os.CreateTemp("./", "homebox-db-restore-*.db")
		if err != nil {
			return fmt.Errorf("failed to create temporary file: %w", err)
		}
		tempPath := tempFile.Name()
		defer tempFile.Close()

		// Copy backup database to temporary location
		backupFile, err := os.Open(backupPath)
		if err != nil {
			os.Remove(tempPath)
			return fmt.Errorf("failed to open backup database: %w", err)
		}
		defer backupFile.Close()

		_, err = io.Copy(tempFile, backupFile)
		if err != nil {
			os.Remove(tempPath)
			return fmt.Errorf("failed to copy backup database: %w", err)
		}

		// Create restart marker with temporary database path
		restartMarker := filepath.Join("./", "RESTART_REQUIRED_DB")
		if err := os.WriteFile(restartMarker, []byte(tempPath), 0644); err != nil {
			os.Remove(tempPath)
			return fmt.Errorf("failed to create restart marker: %w", err)
		}

		return server.JSON(w, http.StatusOK, map[string]string{
			"message": "database import prepared - please restart the service to complete",
			"restart_required": "true",
		})
	}
}

// HandleDatabaseBackupsList godoc
//
//	@Summary	List Available Database Backup Files
//	@Tags		Tools
//	@Produce	json
//	@Success	200	{object}	map[string][]string	"{\"files\": [\"backup1.db\", \"backup2.db\"]}"
//	@Router		/v1/tools/database/backups [GET]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDatabaseBackupsList() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		backupDir := "./backup"

		// Check if backup directory exists
		if _, err := os.Stat(backupDir); os.IsNotExist(err) {
			return server.JSON(w, http.StatusOK, map[string][]string{
				"files": {},
			})
		}

		// Read backup directory
		files, err := os.ReadDir(backupDir)
		if err != nil {
			return fmt.Errorf("failed to read backup directory: %w", err)
		}

		var fileNames []string
		for _, file := range files {
			if !file.IsDir() && filepath.Ext(file.Name()) == ".db" {
				fileNames = append(fileNames, file.Name())
			}
		}

		return server.JSON(w, http.StatusOK, map[string][]string{
			"files": fileNames,
		})
	}
}

// HandleDataBackupsList godoc
//
//	@Summary	List Available Data Backup Files
//	@Tags		Tools
//	@Produce	json
//	@Success	200	{object}	map[string][]string	"{\"files\": [\"backup1.zip\", \"backup2.zip\"]}"
//	@Router		/v1/tools/data/backups [GET]
//	@Security	Bearer
func (ctrl *V1Controller) HandleDataBackupsList() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		backupDir := "./backup"

		// Check if backup directory exists
		if _, err := os.Stat(backupDir); os.IsNotExist(err) {
			return server.JSON(w, http.StatusOK, map[string][]string{
				"files": {},
			})
		}

		// Read backup directory
		files, err := os.ReadDir(backupDir)
		if err != nil {
			return fmt.Errorf("failed to read backup directory: %w", err)
		}

		var fileNames []string
		for _, file := range files {
			if !file.IsDir() && filepath.Ext(file.Name()) == ".zip" {
				fileNames = append(fileNames, file.Name())
			}
		}

		return server.JSON(w, http.StatusOK, map[string][]string{
			"files": fileNames,
		})
	}
}

// createBackup creates a backup from source directory to destination file
func createBackup(srcDir, dstFile string) error {
	backupFile, err := os.Create(dstFile)
	if err != nil {
		return err
	}
	defer backupFile.Close()

	zipWriter := zip.NewWriter(backupFile)
	defer zipWriter.Close()

	return filepath.Walk(srcDir, func(path string, info os.FileInfo, err error) error {
		if err != nil {
			return err
		}

		if info.IsDir() {
			return nil
		}

		// 获取相对于源目录的路径
		relPath, err := filepath.Rel(srcDir, path)
		if err != nil {
			return err
		}

		// 跳过根目录下的文件（如homebox.db等），只导出子目录中的文件
		if filepath.Dir(relPath) == "." {
			return nil // 跳过根目录文件
		}

		header, err := zip.FileInfoHeader(info)
		if err != nil {
			return err
		}

		header.Name = filepath.ToSlash(relPath)

		writer, err := zipWriter.CreateHeader(header)
		if err != nil {
			return err
		}

		srcFile, err := os.Open(path)
		if err != nil {
			return err
		}
		defer srcFile.Close()

		_, err = io.Copy(writer, srcFile)
		return err
	})
}

// extractZIP extracts ZIP file to destination directory
func extractZIP(src io.Reader, dstDir string) error {
	// Create temporary file to read from reader
	tempFile, err := os.CreateTemp("", "temp-zip-*")
	if err != nil {
		return fmt.Errorf("failed to create temporary file: %w", err)
	}
	defer os.Remove(tempFile.Name())
	defer tempFile.Close()

	// Copy content to temp file
	_, err = io.Copy(tempFile, src)
	if err != nil {
		return err
	}

	// Seek to beginning
	_, err = tempFile.Seek(0, 0)
	if err != nil {
		return err
	}

	// Get file info for size
	stat, err := tempFile.Stat()
	if err != nil {
		return err
	}

	// Create zip reader
	zipReader, err := zip.NewReader(tempFile, stat.Size())
	if err != nil {
		return err
	}

	for _, file := range zipReader.File {
		filePath := filepath.Join(dstDir, file.Name)

		// Skip root level database files to avoid Windows file locking issues
		baseName := filepath.Base(file.Name)
		if !file.FileInfo().IsDir() && 
		   (baseName == "homebox.db" || baseName == "homebox.db-shm" || baseName == "homebox.db-wal") &&
		   filepath.Dir(file.Name) == "." {
			continue
		}

		// Create directory if needed
		if file.FileInfo().IsDir() {
			os.MkdirAll(filePath, file.Mode())
			continue
		}

		// Create parent directory
		if err := os.MkdirAll(filepath.Dir(filePath), 0755); err != nil {
			return fmt.Errorf("failed to create directory %s: %w", filepath.Dir(filePath), err)
		}

		// Create destination file
		dstFile, err := os.OpenFile(filePath, os.O_WRONLY|os.O_CREATE|os.O_TRUNC, file.Mode())
		if err != nil {
			return fmt.Errorf("failed to create file %s: %w", filePath, err)
		}

		// Copy file content
		srcFile, err := file.Open()
		if err != nil {
			dstFile.Close()
			return err
		}

		_, err = io.Copy(dstFile, srcFile)
		srcFile.Close()
		dstFile.Close()

		if err != nil {
			return err
		}
	}

	return nil
}

// HandleSQLDumpExportDownload godoc
//
//	@Summary	Export Database as SQL Dump for Download
//	@Tags		Tools
//	@Produce	application/sql
//	@Success	200	{string}	string	"application/sql"
//	@Router		/v1/tools/database/sqldump/export/download [GET]
//	@Security	Bearer
func (ctrl *V1Controller) HandleSQLDumpExportDownload() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Get raw database connection
		db := ctrl.repo.DB().Sql()
		
		// Create SQL dumper with default options
		options := sqldump.DefaultDumpOptions()
		dumper := sqldump.NewSQLDumper(db, options)
		
		// Set headers for file download
		filename := fmt.Sprintf("homebox-sqldump-%s.sql", time.Now().Format("2006-01-02-15-04-05"))
		w.Header().Set("Content-Type", "application/sql")
		w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", filename))
		
		// Dump SQL directly to response
		if err := dumper.DumpToWriter(w); err != nil {
			return fmt.Errorf("failed to create SQL dump: %w", err)
		}
		
		return nil
	}
}

// HandleSQLDumpExportSave godoc
//
//	@Summary	Export Database as SQL Dump and Save to Backup Directory
//	@Tags		Tools
//	@Produce	json
//	@Success	200	{object}	map[string]string	"{\"message\": \"sql dump created\", \"filename\": \"homebox-sqldump-2024-01-01-12-00-00.sql\"}"
//	@Router		/v1/tools/database/sqldump/export/save [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleSQLDumpExportSave() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Create backup directory if it doesn't exist
		backupDir := "./backup"
		if err := os.MkdirAll(backupDir, 0755); err != nil {
			return fmt.Errorf("failed to create backup directory: %w", err)
		}
		
		// Generate filename with current timestamp
		filename := fmt.Sprintf("homebox-sqldump-%s.sql", time.Now().Format("2006-01-02-15-04-05"))
		backupPath := filepath.Join(backupDir, filename)
		
		// Create backup file
		backupFile, err := os.Create(backupPath)
		if err != nil {
			return fmt.Errorf("failed to create backup file: %w", err)
		}
		defer backupFile.Close()
		
		// Get raw database connection
		db := ctrl.repo.DB().Sql()
		
		// Create SQL dumper with default options
		options := sqldump.DefaultDumpOptions()
		dumper := sqldump.NewSQLDumper(db, options)
		
		// Dump SQL to file
		if err := dumper.DumpToWriter(backupFile); err != nil {
			return fmt.Errorf("failed to create SQL dump: %w", err)
		}
		
		// Return success response
		return server.JSON(w, http.StatusOK, map[string]string{
			"message":  "sql dump created",
			"filename": filename,
		})
	}
}

// HandleSQLDumpImportUpload godoc
//
//	@Summary	Import SQL Dump from Uploaded File
//	@Tags		Tools
//	@Accept		multipart/form-data
//	@Param		file	formData	file	true	"SQL dump file (.sql)"
//	@Produce	json
//	@Success	200	{object}	map[string]interface{}	"{\"message\": \"sql dump imported\", \"statements_executed\": 150}"
//	@Router		/v1/tools/database/sqldump/import/upload [POST]
//	@Security	Bearer
// HandleSQLDumpImportUpload godoc
//
//	@Summary	Import SQL Dump from Uploaded File
//	@Tags		Tools
//	@Accept		multipart/form-data
//	@Param		file	formData	file	true	"SQL dump file (.sql)"
//	@Param		replaceExisting	formData	bool	false	"Replace existing records"
//	@Param		skipDuplicates	formData	bool	false	"Skip duplicate records"
//	@Param		clearBeforeImport	formData	bool	false	"Clear database before import"
//	@Param		ignoreErrors	formData	bool	false	"Continue on errors"
//	@Produce	json
//	@Success	200	{object}	map[string]interface{}	"{\"message\": \"sql dump imported\"}"
//	@Router		/v1/tools/database/sqldump/import/upload [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleSQLDumpImportUpload() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Parse multipart form
		err := r.ParseMultipartForm(50 << 20) // 50MB max
		if err != nil {
			return fmt.Errorf("failed to parse multipart form: %w", err)
		}
		
		// Get uploaded file
		file, header, err := r.FormFile("file")
		if err != nil {
			return fmt.Errorf("failed to get uploaded file: %w", err)
		}
		defer file.Close()
		
		// Validate file extension
		if !strings.HasSuffix(header.Filename, ".sql") {
			return fmt.Errorf("only .sql files are allowed")
		}
		
		// Get import options from form parameters
		options := sqldump.DefaultImportOptions()
		
		// Parse form parameters for options
		if r.FormValue("replaceExisting") == "true" {
			options.ReplaceExisting = true
		}
		if r.FormValue("skipDuplicates") == "true" {
			options.SkipDuplicates = true
		}
		if r.FormValue("clearBeforeImport") == "true" {
			options.ClearBeforeImport = true
		}
		if r.FormValue("ignoreErrors") == "true" {
			options.IgnoreErrors = true
		}
		
		// Always enable foreign key handling and duplicate skipping by default
		options.DisableForeignKeys = true
		if !options.ReplaceExisting && !options.ClearBeforeImport {
			options.SkipDuplicates = true
		}
		
		// Get raw database connection
		db := ctrl.repo.DB().Sql()
		
		// Create backup before import if not clearing
		if !options.ClearBeforeImport {
			backupTimestamp := time.Now().Format("2006-01-02-15-04-05")
			backupName := fmt.Sprintf("pre-sqldump-import-backup-%s.db", backupTimestamp)
			backupPath := filepath.Join("./backup", backupName)
			
			// Create backup directory if needed
			if err := os.MkdirAll("./backup", 0755); err != nil {
				return fmt.Errorf("failed to create backup directory: %w", err)
			}
			
			// Get current database path
			dbPath := ctrl.config.Database.SqlitePath
			if dbPath == "" {
				dbPath = "./data/homebox.db"
			} else {
				// Remove query parameters
				dbPath = strings.Split(dbPath, "?")[0]
			}
			
			// Create backup if database file exists
			if _, err := os.Stat(dbPath); err == nil {
				srcFile, err := os.Open(dbPath)
				if err != nil {
					return fmt.Errorf("failed to open current database: %w", err)
				}
				defer srcFile.Close()
				
				backupFile, err := os.Create(backupPath)
				if err != nil {
					return fmt.Errorf("failed to create backup file: %w", err)
				}
				defer backupFile.Close()
				
				_, err = io.Copy(backupFile, srcFile)
				if err != nil {
					return fmt.Errorf("failed to backup current database: %w", err)
				}
			}
		}
		
		// Create SQL importer with configured options
		importer := sqldump.NewSQLImporter(db, options)
		
		// Import SQL from uploaded file
		result, err := importer.ImportFromReader(file)
		if err != nil {
			return fmt.Errorf("failed to import SQL dump: %w", err)
		}
		
		// Build response with detailed information
		response := map[string]interface{}{
			"message":              "SQL dump import completed",
			"statements_executed":  result.StatementsExecuted,
			"statements_skipped":   result.StatementsSkipped,
			"records_inserted":     result.RecordsInserted,
			"records_updated":      result.RecordsUpdated,
			"records_skipped":      result.RecordsSkipped,
			"duration":             result.Duration,
			"success":              result.Success,
			"options_used": map[string]bool{
				"replace_existing":    options.ReplaceExisting,
				"skip_duplicates":     options.SkipDuplicates,
				"clear_before_import": options.ClearBeforeImport,
				"ignore_errors":       options.IgnoreErrors,
				"disable_foreign_keys": options.DisableForeignKeys,
			},
		}
		
		// Add tables cleared information if applicable
		if len(result.TablesCleared) > 0 {
			response["tables_cleared"] = result.TablesCleared
		}
		
		// Add errors if any
		if len(result.Errors) > 0 {
			response["errors"] = result.Errors
		}
		
		// Add warnings if any
		if len(result.Warnings) > 0 {
			response["warnings"] = result.Warnings
		}
		
		return server.JSON(w, http.StatusOK, response)
	}
}

// HandleSQLDumpClearDatabase godoc
//
//	@Summary	Clear All Database Data (SQL DUMP)
//	@Tags		Tools
//	@Accept		json
//	@Param		confirmation	body	string	true	"Confirmation string (must be 'CLEAR_ALL_DATA')"
//	@Produce	json
//	@Success	200	{object}	map[string]interface{}	"{\"message\": \"database cleared\"}"
//	@Router		/v1/tools/database/sqldump/clear [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleSQLDumpClearDatabase() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Parse request body to get confirmation
		var req struct {
			Confirmation string `json:"confirmation"`
		}
		
		if err := server.Decode(r, &req); err != nil {
			return fmt.Errorf("failed to decode request: %w", err)
		}
		
		// Require explicit confirmation
		if req.Confirmation != "CLEAR_ALL_DATA" {
			return fmt.Errorf("invalid confirmation string. Must be 'CLEAR_ALL_DATA'")
		}
		
		// Create backup before clearing
		backupTimestamp := time.Now().Format("2006-01-02-15-04-05")
		backupName := fmt.Sprintf("pre-clear-backup-%s.db", backupTimestamp)
		backupPath := filepath.Join("./backup", backupName)
		
		// Create backup directory if needed
		if err := os.MkdirAll("./backup", 0755); err != nil {
			return fmt.Errorf("failed to create backup directory: %w", err)
		}
		
		// Get current database path
		dbPath := ctrl.config.Database.SqlitePath
		if dbPath == "" {
			dbPath = "./data/homebox.db"
		} else {
			// Remove query parameters
			dbPath = strings.Split(dbPath, "?")[0]
		}
		
		// Create backup if database file exists
		if _, err := os.Stat(dbPath); err == nil {
			srcFile, err := os.Open(dbPath)
			if err != nil {
				return fmt.Errorf("failed to open current database: %w", err)
			}
			defer srcFile.Close()
			
			backupFile, err := os.Create(backupPath)
			if err != nil {
				return fmt.Errorf("failed to create backup file: %w", err)
			}
			defer backupFile.Close()
			
			_, err = io.Copy(backupFile, srcFile)
			if err != nil {
				return fmt.Errorf("failed to backup current database: %w", err)
			}
		}
		
		// Get raw database connection
		db := ctrl.repo.DB().Sql()
		
		// Create SQL importer for clearing database
		options := sqldump.DefaultImportOptions()
		options.DisableForeignKeys = true // Temporarily disable foreign keys for clearing
		importer := sqldump.NewSQLImporter(db, options)
		
		// Clear the database
		result, err := importer.ClearDatabase()
		if err != nil {
			return fmt.Errorf("failed to clear database: %w", err)
		}
		
		// Build response with detailed information
		response := map[string]interface{}{
			"message":           "Database cleared successfully",
			"tables_cleared":    result.TablesCleared,
			"records_deleted":   result.RecordsDeleted,
			"duration":          result.Duration,
			"success":           result.Success,
			"backup_created":    backupName,
			"backup_path":       backupPath,
		}
		
		// Add warnings if any
		if len(result.Warnings) > 0 {
			response["warnings"] = result.Warnings
		}
		
		// Add errors if any
		if len(result.Errors) > 0 {
			response["errors"] = result.Errors
		}
		
		return server.JSON(w, http.StatusOK, response)
	}
}

// HandleSQLDumpImportBackup godoc
//
//	@Summary	Import SQL Dump from Backup File
//	@Tags		Tools
//	@Produce	json
//	@Param		filename	query	string	true	"SQL dump backup filename to import"
//	@Success	200	{object}	map[string]interface{}	"{\"message\": \"sql dump imported\"}"
//	@Router		/v1/tools/database/sqldump/import/backup [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleSQLDumpImportBackup() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Get filename from query parameter
		filename := r.URL.Query().Get("filename")
		if filename == "" {
			return fmt.Errorf("filename is required")
		}
		
		// Validate filename to prevent directory traversal
		if strings.Contains(filename, "..") || strings.Contains(filename, "/") || strings.Contains(filename, "\\") {
			return fmt.Errorf("invalid filename")
		}
		
		// Check if it's a SQL file
		if !strings.HasSuffix(filename, ".sql") {
			return fmt.Errorf("only .sql files are allowed")
		}
		
		backupDir := "./backup"
		backupPath := filepath.Join(backupDir, filename)
		
		// Check if backup file exists
		if _, err := os.Stat(backupPath); os.IsNotExist(err) {
			return fmt.Errorf("backup sql dump file does not exist")
		}
		
		// Open backup file
		backupFile, err := os.Open(backupPath)
		if err != nil {
			return fmt.Errorf("failed to open backup file: %w", err)
		}
		defer backupFile.Close()
		
		// Get raw database connection
		db := ctrl.repo.DB().Sql()
		
		// Create SQL importer with default options
		options := sqldump.DefaultImportOptions()
		importer := sqldump.NewSQLImporter(db, options)
		
		// Import SQL from backup file
		result, err := importer.ImportFromReader(backupFile)
		if err != nil {
			return fmt.Errorf("failed to import SQL dump: %w", err)
		}
		
		response := map[string]interface{}{
			"message":              "sql dump imported",
			"statements_executed":  result.StatementsExecuted,
			"statements_skipped":   result.StatementsSkipped,
			"success":              result.Success,
		}
		
		if len(result.Errors) > 0 {
			response["errors"] = result.Errors
		}
		
		return server.JSON(w, http.StatusOK, response)
	}
}

// HandleSQLDumpValidate godoc
//
//	@Summary	Validate SQL Dump File
//	@Tags		Tools
//	@Accept		multipart/form-data
//	@Param		file	formData	file	true	"SQL dump file (.sql)"
//	@Produce	json
//	@Success	200	{object}	map[string]interface{}	"{\"valid\": true, \"errors\": []}"
//	@Router		/v1/tools/database/sqldump/validate [POST]
//	@Security	Bearer
func (ctrl *V1Controller) HandleSQLDumpValidate() errchain.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) error {
		_ = services.UseUserCtx(r.Context())
		
		// Parse multipart form
		err := r.ParseMultipartForm(50 << 20) // 50MB max
		if err != nil {
			return fmt.Errorf("failed to parse multipart form: %w", err)
		}
		
		// Get uploaded file
		file, header, err := r.FormFile("file")
		if err != nil {
			return fmt.Errorf("failed to get uploaded file: %w", err)
		}
		defer file.Close()
		
		// Validate file extension
		if !strings.HasSuffix(header.Filename, ".sql") {
			return fmt.Errorf("only .sql files are allowed")
		}
		
		// Get raw database connection
		db := ctrl.repo.DB().Sql()
		
		// Create SQL importer for validation
		options := sqldump.DefaultImportOptions()
		importer := sqldump.NewSQLImporter(db, options)
		
		// Validate SQL
		errors, err := importer.ValidateSQL(file)
		if err != nil {
			return fmt.Errorf("failed to validate SQL dump: %w", err)
		}
		
		response := map[string]interface{}{
			"valid":  len(errors) == 0,
			"errors": errors,
		}
		
		return server.JSON(w, http.StatusOK, response)
	}
}

// extractZIPWithDB extracts ZIP file including database files to destination directory
func extractZIPWithDB(src io.Reader, dstDir string) error {
	// Create temporary file to read from reader
	tempFile, err := os.CreateTemp("", "temp-zip-*")
	if err != nil {
		return fmt.Errorf("failed to create temporary file: %w", err)
	}
	defer os.Remove(tempFile.Name())
	defer tempFile.Close()

	// Copy content to temp file
	_, err = io.Copy(tempFile, src)
	if err != nil {
		return err
	}

	// Seek to beginning
	_, err = tempFile.Seek(0, 0)
	if err != nil {
		return err
	}

	// Get file info for size
	stat, err := tempFile.Stat()
	if err != nil {
		return err
	}

	// Create zip reader
	zipReader, err := zip.NewReader(tempFile, stat.Size())
	if err != nil {
		return err
	}

	for _, file := range zipReader.File {
		filePath := filepath.Join(dstDir, file.Name)

		// Create directory if needed
		if file.FileInfo().IsDir() {
			os.MkdirAll(filePath, file.Mode())
			continue
		}

		// Create parent directory
		if err := os.MkdirAll(filepath.Dir(filePath), 0755); err != nil {
			return fmt.Errorf("failed to create directory %s: %w", filepath.Dir(filePath), err)
		}

		// Create destination file - 包括数据库文件
		dstFile, err := os.OpenFile(filePath, os.O_WRONLY|os.O_CREATE|os.O_TRUNC, file.Mode())
		if err != nil {
			return fmt.Errorf("failed to create file %s: %w", filePath, err)
		}

		// Copy file content
		srcFile, err := file.Open()
		if err != nil {
			dstFile.Close()
			return err
		}

		_, err = io.Copy(dstFile, srcFile)
		srcFile.Close()
		dstFile.Close()

		if err != nil {
			return err
		}
	}

	return nil
}